package sh.wn36.vehiclemanage.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import sh.wn36.vehiclemanage.entity.FeesManager;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import sh.wn36.vehiclemanage.xzh.FeeInfo;
import sh.wn36.vehiclemanage.xzh.RepairInfo;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xk
 * @since 2021-04-26
 */
public interface FeesManagerMapper extends BaseMapper<FeesManager> {
    @Select("select f.*,c.car_no,cu.unit_name,q.rname from fees_manager f\n" +
            "LEFT JOIN car c on c.id = f.CAR_ID\n" +
            "LEFT JOIN current_unit cu on cu.id=f.FEES_UNIT\n" +
            "LEFT JOIN q_users q on q.id = f.OPERATOR\n" +
            "        where c.car_no like concat(\"%\",#{text},\"%\")")
    IPage<FeeInfo> getListByCarNo(Page<RepairInfo> page, @Param("text") String text);
    @Select("select f.*,c.car_no,cu.unit_name,q.rname from fees_manager f\n" +
            "LEFT JOIN car c on c.id = f.CAR_ID\n" +
            "LEFT JOIN current_unit cu on cu.id=f.FEES_UNIT\n" +
            "LEFT JOIN q_users q on q.id = f.OPERATOR\n" +
            "where cu.unit_name like concat(\"%\",#{text},\"%\")")
    IPage<FeeInfo> getListByUnitName(Page<RepairInfo> page, @Param("text") String text);
    @Select("select f.*,c.car_no,cu.unit_name,q.rname from fees_manager f\n" +
            "LEFT JOIN car c on c.id = f.CAR_ID\n" +
            "LEFT JOIN current_unit cu on cu.id=f.FEES_UNIT\n" +
            "LEFT JOIN q_users q on q.id = f.OPERATOR\n" +
            "where q.rname like concat(\"%\",#{text},\"%\")")
    IPage<FeeInfo> getListByRname(Page<RepairInfo> page,  @Param("text")String text);
    @Select("select f.*,c.car_no,cu.unit_name,q.rname from fees_manager f\n" +
            "LEFT JOIN car c on c.id = f.CAR_ID\n" +
            "LEFT JOIN current_unit cu on cu.id=f.FEES_UNIT\n" +
            "LEFT JOIN q_users q on q.id = f.OPERATOR")
    IPage<FeeInfo> getList(Page<RepairInfo> page);
    @Select("select f.*,c.car_no,cu.unit_name,q.rname from fees_manager f\n" +
            "LEFT JOIN car c on c.id = f.CAR_ID\n" +
            "LEFT JOIN current_unit cu on cu.id=f.FEES_UNIT\n" +
            "LEFT JOIN q_users q on q.id = f.OPERATOR\n"+
            "where r.id = #{id}")
    FeeInfo getDetailById(@Param("id") Integer id);
    @Update("update fees_manager set  \n" +
            "CAR_ID=#{carId},\n" +
            "FEES_NAME = #{feesName},\n" +
            "FEES_DATE=#{feesDate},\n" +
            "FEES_AMOUNT = #{feesAmount},\n" +
            "FEES_UNIT= #{feesUnit} ,\n" +
            "OPERATOR= #{operator},\n" +
            "REMARKS= #{remarks}\n" +
            "where id = #{id}")
    int updateFees(@Param("id") Integer id,
                   @Param("carId") Integer carId,
                   @Param("feesName") String feesName,
                   @Param("feesDate") Date feesDate,
                   @Param("feesAmount") BigDecimal feesAmount,
                   @Param("feesUnit") Integer feesUnit,
                   @Param("operator") Integer operator,
                   @Param("remarks") String remarks);
    @Select("SELECT\n" +
            "c.car_no ,\n" +
            "IFNULL(SUM(f.FEES_AMOUNT),0) AS car_expenses,\n" +
            "'规费管理表' AS '数据来源'\n" +
            "FROM\n" +
            "fees_manager f\n" +
            "LEFT JOIN car c ON f.CAR_ID= c.id\n" +
            "GROUP BY c.car_no\n" +
            "HAVING car_expenses!=0;")
    List<Map<String, Object>> getTable();


    @Select("SELECT\n" +
            "c.car_no ,\n" +
            "IFNULL(SUM(f.FEES_AMOUNT),0) AS car_expenses,\n" +
            "'规费管理表' AS '数据来源'\n" +
            "FROM\n" +
            "fees_manager f\n" +
            "LEFT JOIN car c ON f.CAR_ID= c.id\n" +
            "where c.id = #{id}\n" +
            "GROUP BY c.car_no\n" +
            "HAVING car_expenses!=0;")
    List<Map<String, Object>> getTableByCarId(@Param("id") Integer id);
}
